/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.sadhar.sipp.unkerja;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import org.sadhar.errhandler.ClassAntiNull;
import org.sadhar.sia.common.ClassConnection;
import org.springframework.jdbc.core.RowMapper;

/**
 *
 * @author Deny Prasetyo
 */
public class UnKerjaDAOImpl implements UnKerjaDAO {

    public UnKerjaDAOImpl() {
        ClassConnection.getTransactionProxyFactoryBean().setTarget(this);
    }

    public List<UnKerja> list() {
        return ClassConnection.getJdbc().queryForList("SELECT * FROM kamus.unkerja");
    }

    public UnKerja select(String kdUnitKerja) {
        String sql = "SELECT * FROM kamus.unkerja WHERE Kd_unit_kerja=?";
        return (UnKerja) ClassConnection.getJdbc().queryForObject(sql, new Object[]{kdUnitKerja},
                new RowMapper() {

                    public Object mapRow(ResultSet rs, int rowNum)
                            throws SQLException {
                        return new UnKerja(
                                rs.getString("Kd_unit_kerja"),
                                rs.getString("Nama_unit_kerja"),
                                rs.getString("No_rekening"),
                                rs.getString("penanggungjawab"),
                                rs.getString("singkatan"));
                    }
                });
    }

    public List<UnKerja> listpop(String key) throws Exception {
        String sql = "SELECT * FROM kamus.unkerja WHERE Kd_unit_kerja LIKE ? OR Nama_unit_kerja LIKE ?";
        List<UnKerja> list = new ArrayList<UnKerja>();
        List<Map> rows = ClassConnection.getJdbc().queryForList(sql, new Object[]{key, "%" + key + "%"});
        for (Map m : rows) {
            UnKerja unKerja = new UnKerja();
            unKerja.setKdUnitKerja(m.get("Kd_unit_kerja").toString());
            unKerja.setNamaUnitKerja(m.get("Nama_unit_kerja").toString());
            list.add(unKerja);
        }
        return list;
    }

    public List<UnKerja> listpopbanpem(String key) throws Exception {
        String sql = "SELECT uk.Nama_unit_kerja,kodeUnitKerja "
                + " FROM ppmk.posAnggaranPendapatanUnit AS papu "
                + " INNER JOIN kamus.unkerja AS uk ON (uk.Kd_unit_kerja=papu.kodeUnitKerja) "
                + " WHERE idPosPendapatan='5' and uk.`Nama_unit_kerja` like ?";
        List<UnKerja> list = new ArrayList<UnKerja>();
        List<Map> rows = ClassConnection.getJdbc().queryForList(sql, new Object[]{ "%" + key + "%"});
        for (Map m : rows) {
            UnKerja unKerja = new UnKerja();
            unKerja.setKdUnitKerja(m.get("kodeUnitKerja").toString());
            unKerja.setNamaUnitKerja(m.get("Nama_unit_kerja").toString());
            list.add(unKerja);
        }
        return list;
    }

    public List<UnKerja> list(int idPosPendapatan, String tahunAnggaran) throws Exception {
        String sql = "SELECT un.Kd_unit_kerja,un.nama_unit_kerja,ap.idPosAnggaranPendapatanUnit FROM ppmk.posAnggaranPendapatanUnit AS ap INNER "
                + "JOIN kamus.unkerja AS un ON ap.kodeUnitKerja=un.Kd_unit_kerja WHERE "
                + "idPosPendapatan=? AND tahunAnggaran=?";
        List<UnKerja> list = new ArrayList<UnKerja>();
        List<Map> rows = ClassConnection.getJdbc().queryForList(sql, new Object[]{
                    idPosPendapatan, tahunAnggaran
                });
        for (Map m : rows) {
            UnKerja unKerja = new UnKerja();
            unKerja.setNamaUnitKerja(m.get("nama_unit_kerja").toString());
            unKerja.setKdUnitKerja(m.get("Kd_unit_kerja").toString());
            unKerja.setIdPosAnggaranPendapatanUnit(ClassAntiNull.AntiNullInt(m.get("idPosAnggaranPendapatanUnit")));
            list.add(unKerja);
        }
        return list;
    }

    public List<UnKerja> listAll(String keySearch) throws Exception {
        List<UnKerja> list = new ArrayList<UnKerja>();
        List<Map> rows = ClassConnection.getJdbc().queryForList("SELECT * FROM kamus.unkerja WHERE Kd_unit_kerja LIKE ? OR "
                + "Nama_unit_kerja LIKE ?", new Object[]{
                    "%" + keySearch + "%",
                    "%" + keySearch + "%"
                });
        for (Map m : rows) {
            UnKerja un = new UnKerja();
            un.setKdUnitKerja(ClassAntiNull.AntiNullString(m.get("Kd_unit_kerja")));
            un.setNamaUnitKerja(ClassAntiNull.AntiNullString(m.get("Nama_unit_kerja")));
            list.add(un);
        }
        return list;
    }
}
